package com.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import com.javabean.UserBean;
import com.sun.org.apache.xerces.internal.impl.xpath.XPath.Step;

public class User {
	public List getInfo(String username){
		List list = new ArrayList();
		
		Connection connection;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from user where username = '"+username+"' ";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			while(rSet.next()){
				UserBean uBean = new UserBean();
				uBean.setIduser(rSet.getString("iduser"));
				uBean.setUsername(rSet.getString("username"));
				uBean.setPassword(rSet.getString("password"));
				uBean.setLevel(rSet.getString("level"));
				uBean.setName(rSet.getString("name"));
				uBean.setPhone(rSet.getString("phone"));
				uBean.setEmail(rSet.getString("email"));
				uBean.setAge(rSet.getString("age"));
				uBean.setSex(rSet.getString("sex"));
				list.add(uBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			sManege.dbclose(connection, statement, rSet);
		}
		
		return list;
	}
	public List getOnefo(String id){
		List list = new ArrayList();
		
		Connection connection;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from user where iduser = '"+id+"' ";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			while(rSet.next()){
				UserBean uBean = new UserBean();
				uBean.setIduser(rSet.getString("iduser"));
				uBean.setUsername(rSet.getString("username"));
				uBean.setPassword(rSet.getString("password"));
				uBean.setLevel(rSet.getString("level"));
				uBean.setName(rSet.getString("name"));
				uBean.setPhone(rSet.getString("phone"));
				uBean.setEmail(rSet.getString("email"));
				uBean.setAge(rSet.getString("age"));
				uBean.setSex(rSet.getString("sex"));
				list.add(uBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			sManege.dbclose(connection, statement, rSet);
		}
		
		return list;
	}
	public void addUser(UserBean userBean){
		Connection connection;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "insert into user (username,password,name,email,phone) values('"+userBean.getUsername()+"','"+userBean.getPassword()+"','"+userBean.getName()+"','"+userBean.getEmail()+"','"+userBean.getPhone()+"')";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sManege.dbclose(connection, statement);
	}
	public List listUser(String level){
		List userlist = new ArrayList();
		
		Connection connection;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "select * from user where level >="+Integer.parseInt(level)+"";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			while(rSet.next()){
				UserBean uBean = new UserBean();
				uBean.setIduser(rSet.getString("iduser"));
				uBean.setUsername(rSet.getString("username"));
				uBean.setPassword(rSet.getString("password"));
				uBean.setLevel(rSet.getString("level"));
				uBean.setName(rSet.getString("name"));
				uBean.setPhone(rSet.getString("phone"));
				uBean.setEmail(rSet.getString("email"));
				uBean.setAge(rSet.getString("age"));
				uBean.setSex(rSet.getString("sex"));
				userlist.add(uBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			sManege.dbclose(connection, statement, rSet);
		}
		return userlist;
	}
	public void updateUser( UserBean userBean){
		Connection connection;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "Update user set username = '"+userBean.getUsername()+"',password = '"+userBean.getPassword()+"',name = '"+userBean.getName()+"',email = '"+userBean.getEmail()+"'"
				+ ",phone = '"+userBean.getPhone()+"',sex = '"+userBean.getSex()+"',age = '"+userBean.getAge()+"',level = '"+userBean.getLevel()+"' where iduser = '"+userBean.getIduser()+"'";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sManege.dbclose(connection, statement);
	}
	public void deleteUser(String id){
		Connection connection;
		Statement statement = null;
		ResultSet rSet = null;
		String sql = "delete from user where iduser = '"+id+"' ";
		SqlManege sManege = new SqlManege();
		connection = sManege.doInit();
		try {
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sManege.dbclose(connection, statement);
	}
}
